 SET NOCOUNT OFF
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

/******************************************************************************/
/* Step 1. Remove stored procedure if it already exists                       */
/******************************************************************************/
IF (SELECT OBJECT_ID('[CaseManagement].[InjuriesForCase]')) IS NOT NULL
BEGIN
  DROP PROCEDURE [CaseManagement].[InjuriesForCase]
END

GO

/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE [CaseManagement].[InjuriesForCase]( @CaseOID BIGINT )
AS  
BEGIN

	Select  Injuries.OID as 'InjuryOID',
            Injuries.Version as 'InjuryVersion',
            Injuries.CaseID as 'InjuryCaseID',
            Injuries.InjuryID,
            TypesOfInjury.Description as 'InjuryDescription',
            Injuries.UpdatedDate as 'InjuryUpdatedDate',
            Injuries.CreatedDate as 'InjuryCreatedDate'
	From Injuries
	Left Join TypesOfInjury On TypesOfInjury.OID = Injuries.InjuryID
	Where
		Injuries.CaseID = @CaseOID

END 
GO

/******************************************************************************/
/* Step 3. Assign appropriate permissions                                     */
/******************************************************************************/
GRANT EXECUTE ON [CaseManagement].[InjuriesForCase] TO [Liberty_Users]
GO
-- Developers and Admins have permissions to execute through membership